package com.yz.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.springframework.cglib.beans.BeanMap;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;


public class JdbcUtils {

	/**
	 * 链接jdbc
	 * @return
	 */
	public static  Connection getConnection(Map dataSourceMap){

		String driver = (String) dataSourceMap.get("driver");
		String url = (String) dataSourceMap.get("url");
		String username = (String) dataSourceMap.get("username");
		String pass = (String) dataSourceMap.get("pass");

		Connection connection=null;

		 try {
			 Class.forName(driver);
			 connection = DriverManager.getConnection(url,username,pass);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	//查字段
	public static ArrayList<String> getfieldNamesByDatabaseNameAndTableName(Map map,String dataBaseName,String TableName){
		Connection connection = getConnection(map);
		StringBuffer sql = new StringBuffer();
		sql.append("select COLUMN_NAME as fieldNames from information_schema.COLUMNS where table_name = ");
		sql.append("'");
		sql.append(TableName);
		sql.append("'");
		sql.append("and table_schema = ");
		sql.append("'");
		sql.append(dataBaseName);
		sql.append("'");
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
		try {
            preparedStatement = connection.prepareStatement(sql.toString());
			resultSet = preparedStatement.executeQuery();
			ArrayList<String> fields = new ArrayList<>();

			while (resultSet.next()){
				fields.add(resultSet.getString(1));
			}


			return fields;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            close(resultSet,preparedStatement,connection);
		}
		return null;
	}


	//生成建表语句 整库迁移
	public static void buildTableSql(Map dataSourceMap,Map targetMap, String sourceTableName,String targetTableName){
		StringBuffer buildTableSql = new StringBuffer();
		buildTableSql.append("show create table ");
		buildTableSql.append(sourceTableName);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
			List<Map> queryData = JdbcUtils.queryData(dataSourceMap, buildTableSql.toString());

			Map map = queryData.get(0);
			//System.out.println(queryData.get(0).toString());
			String sql = map.toString().substring(map.toString().indexOf(",")+1);
			sql = sql.substring(sql.indexOf("=")+1,sql.length()-1);

			//System.out.println(sql);
			connection = getConnection(targetMap);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
			System.out.println("新建表:"+targetTableName);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            close(resultSet,preparedStatement,connection);
        }


	}

	/**
	 * 生成建表语句
	 * @param dataSourceMap  源数据库
	 * @param targetMap	目标数据库
	 * @param sourceTableName 	表名
	 * @param targetTableName	表名
	 * @param fieldNames 选择字段名称
	 */
	public static void buildTableSql2(Map dataSourceMap,Map targetMap, String sourceTableName,String targetTableName,String fieldNames){
		String[] fieldNameArray = fieldNames.split(",");
		//1.获取原表DDL
		StringBuffer buildTableSql = new StringBuffer();
		buildTableSql.append("show create table ");
		buildTableSql.append(sourceTableName);

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
		try {
			List<Map> queryData = JdbcUtils.queryData(dataSourceMap, buildTableSql.toString());
			Map map = queryData.get(0);
			String sql = map.toString().substring(map.toString().indexOf(",")+1);
			sql = sql.substring(sql.indexOf("=")+1,sql.length()-1);



			//2.根据字段数据创建新的DDL
			int indexOf = sql.indexOf("(");
			int lastIndexOf = sql.lastIndexOf(")");
			//subSql 中间语句
			String subSql = sql.substring(indexOf+1, lastIndexOf);
			//preSql 前缀语句
			//String preSql = sql.substring(0, indexOf+1);
			String preSql = "CREATE TABLE " + targetTableName + "(";
			//sufSql 后缀语句
			String sufSql = sql.substring(lastIndexOf);

			//每个字段的详情
			String[] fieldDetailArray = subSql.split(",");

			//通过字段详情获取原表的全部字段名	(--最后一行可能是主键,导致主键字段重复--)
			ArrayList<String> sourceField = new ArrayList<>();
			for (String fieldDetail : fieldDetailArray) {
				String[] fieldDetailSplit = fieldDetail.split("`");
				sourceField.add(fieldDetailSplit[1]);
			}

			//拼接新的DDL
			StringBuffer createTableSql = new StringBuffer();
			createTableSql.append(preSql);
			for (String fieldName : fieldNameArray) {
				for (String sourceFieldName : sourceField) {
					if(fieldName.equals(sourceFieldName)){
						int sourceFieldNameIndex = sourceField.indexOf(sourceFieldName);
						createTableSql.append(fieldDetailArray[sourceFieldNameIndex]);
						createTableSql.append(",");
						//在有主键的情况下起作用  使主键语句后加:PRIMARY KEY (`主键名`)
						sourceField.set(sourceFieldNameIndex,"");
					}
				}
			}

			//过渡Sql
			String transitionSql = createTableSql.toString().substring(0, createTableSql.toString().length() - 1);
			createTableSql.setLength(0);
			createTableSql.append(transitionSql);
			createTableSql.append(sufSql);

			System.out.println("建表语句:"+createTableSql.toString());
			//执行建表动作
			connection = getConnection(targetMap);
            preparedStatement = connection.prepareStatement(createTableSql.toString());
            preparedStatement.executeUpdate();
			System.out.println("新建表:"+targetTableName);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            close(resultSet,preparedStatement,connection);
        }


	}


	//查询
	public static List<Map> queryData(Map dataSourceMap,String sql) throws SQLException{
		Date date=new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("迁移开始:"+sdf.format(date));
		Connection connection = getConnection(dataSourceMap);
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet executeQuery = prepareStatement.executeQuery();
		ResultSetMetaData metaData = executeQuery.getMetaData();
		int columnCount = metaData.getColumnCount();
		Map resultmap=null;

		List<Map> resullist=new ArrayList();
		while(executeQuery.next()){
			resultmap =new HashMap();
			for(int i = 1;i<=columnCount;i++){
				String key = metaData.getColumnLabel(i);
				//System.out.println(key+""+executeQuery.getObject(i));
				resultmap.put(key, executeQuery.getObject(i));
			}
			resullist.add(resultmap);
		}

		close(executeQuery, prepareStatement, connection);



		return resullist;
	}

	public static <T> T mapToBean(Map<String, Object> map,T bean) {
		BeanMap beanMap = BeanMap.create(bean);
		beanMap.putAll(map);
		return bean;
	}



	public static void affair(Map dataSourceMap,String sql) throws SQLException {

		Connection connection = getConnection(dataSourceMap);
		connection.setAutoCommit(false);
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		int executeUpdate = prepareStatement.executeUpdate();
		connection.commit();
		close(null, prepareStatement, connection);
		Date date=new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("迁移结束:"+sdf.format(date));

        close(null, null, connection);


	}



	public static void affair2(Map dataSourceMap,List<String> sqlList) throws SQLException {

		Connection connection = getConnection(dataSourceMap);
		connection.setAutoCommit(false);
		Statement createStatement = connection.createStatement();
		for (String string : sqlList) {
			createStatement.addBatch(string);
		}
		int[] executeBatch = createStatement.executeBatch();

		connection.commit();
		createStatement.close();
		close(null, null, connection);
		Date date=new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("迁移结束:"+sdf.format(date));

	}






	public static void main(String[] args) throws SQLException {
		//源数据库
		Map map=new HashMap();
		map.put("driver", "com.mysql.jdbc.Driver");
		map.put("url", "jdbc:mysql://127.0.0.1:3306/miaosha?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC");
		map.put("username", "root");
		map.put("pass", "root");
//
//		queryCount(map,"miaosha_user");
//		List<Map> queryData = queryData(map, "select * from dashuju limit 0,5000000");
//		//System.out.println(queryData.toString());
//
//		//目标数据库
		Map targetMap=new HashMap();
		targetMap.put("driver", "com.mysql.jdbc.Driver");
		targetMap.put("url", "jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC");
		targetMap.put("username", "root");
		targetMap.put("pass", "root");
//		String tableName="dashuju";
//		List<String> checkSql = checkSql(queryData, tableName);
//		//System.out.println(checkSql.toString());
////		for (String sql : checkSql) {
////			System.out.println(sql);
////			affair(targetMap,sql);
////		}
//
//		affair2(targetMap, checkSql);

		//buildTableSql(map,targetMap,"order_info");
		String[] stringArray = {"id","password","register_data","login_count"};
		//buildTableSql2(map,targetMap,"miaosha_user",stringArray);

	}


	//生成sql语句
	public static List<String> checkSql(List<Map> list,String tableName,String fieldNames){

		String[] fieldNameArray = fieldNames.split(",");
		StringBuffer sbuf=new StringBuffer();

		List<String> sqllist =new ArrayList();
		for (Map map : list) {
			sbuf.append("INSERT INTO ");
			sbuf.append(tableName);
			sbuf.append("(");
			Set<String> keySet = map.keySet();
			for (String fieldName : fieldNameArray) {
				sbuf.append(fieldName);
				sbuf.append(",");
			}
			String newString = sbuf.substring(0, sbuf.length()-1);
			sbuf.setLength(0);
			sbuf.append(newString);
			sbuf.append(") values (");

			for (String fieldName : fieldNameArray) {
				for (String key : keySet) {
					if(key.equals(fieldName)){

						if("null".equals(map.get(key)) || StringUtils.isBlank((CharSequence) map.get(key))){
							sbuf.append("NULL");
						}else {
							sbuf.append("'");
							sbuf.append(map.get(key));
							sbuf.append("'");
						}


						sbuf.append(",");
					}
				}




			}
			String newStringVlues = sbuf.substring(0, sbuf.length()-1);
			sbuf.setLength(0);
			sbuf.append(newStringVlues);
			sbuf.append(")");
			sqllist.add(sbuf.toString());
			sbuf.setLength(0);
		}





		return sqllist;

	}





	public static int queryCount(Map dataSourceMap,String tableName){
		Connection connection = getConnection(dataSourceMap);
		StringBuffer querySql = new StringBuffer();
		querySql.append("select count(1) as count from ");
		querySql.append(tableName);
		int count = 0;
		try {
			List<Map> dataSource = queryData(dataSourceMap, querySql.toString());
			Map map = dataSource.get(0);
			count = Integer.parseInt(map.get("count").toString());
			System.out.println(count);


		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            close(null, null, connection);
        }
		return count;
	}



		/**
		 * jdbc关流
		 * @param ex
		 * @param pr
		 * @param co
		 */
		public static void close(ResultSet ex,PreparedStatement pr,Connection co){
			try {
				if (ex!=null){ ex.close();}
				if (pr!=null){pr.close();}
				if (co!=null){co.close();}


			} catch (Exception e2) {
				// TODO: handle exception
			}

		}


	public static List<String> checkSql2(List<Map> dataSourceList, String targetTableName, String corefields) {
		String[] corefieldsArray = corefields.split(",");
		ArrayList<String> oldFields = new ArrayList<>();
		ArrayList<String> newFields = new ArrayList<>();
		for (String corefield : corefieldsArray) {
			String[] field = corefield.split(":");
			oldFields.add(field[0]);
			newFields.add(field[1]);
		}
		StringBuffer sbuf = new StringBuffer();

		List<String> sqllist = new ArrayList();
		for (Map map : dataSourceList) {
			sbuf.append("INSERT INTO ");
			sbuf.append(targetTableName);
			sbuf.append("(");
			for (String fieldName : newFields) {
				sbuf.append("`");
				sbuf.append(fieldName);
				sbuf.append("`");
				sbuf.append(",");
			}
			String newString = sbuf.substring(0, sbuf.length() - 1);
			sbuf.setLength(0);
			sbuf.append(newString);
			sbuf.append(") values (");

			for (int i = 0; i < oldFields.size(); i++) {
				if ("null".equals(map.get(oldFields.get(i))) || StringUtils.isBlank( map.get(oldFields.get(i))+"" )) {
					sbuf.append("NULL");
				} else {
					sbuf.append("'");
					sbuf.append(map.get(oldFields.get(i)));
					sbuf.append("'");
				}


				sbuf.append(",");
			}


			String newStringVlues = sbuf.substring(0, sbuf.length() - 1);
			sbuf.setLength(0);
			sbuf.append(newStringVlues);
			sbuf.append(")");

			sqllist.add(sbuf.toString());
			sbuf.setLength(0);


		}
		return sqllist;
	}


}
